﻿using AppsModel.Data_Layer.Interface;
using AppsModel.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AppsModel.Data_Layer.Concrete
{
    public class dbWorkout : IdbWorkout
    {

        public Workout addWorkout(string name, int categoryId, List<addWorkoutField> other, int userId)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            Workout workout = null;
            try
            {
                conn = Helper.createConnection();

                SqlCommand cmd = new SqlCommand("addWorkout", conn);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@categoryId", categoryId));
                cmd.Parameters.Add(new SqlParameter("@userId", userId));

                rdr = cmd.ExecuteReader();
                int workoutId = 0;
                while (rdr.Read())
                {
                    workoutId = rdr.GetInt32(0);
                }
                rdr.Close();

                List<addWorkoutField> additionalFields = new List<addWorkoutField>();
                if (other != null)
                {
                    foreach (addWorkoutField field in other)
                    {
                        cmd = new SqlCommand("addAdditionalWorkoutField", conn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@workoutId", workoutId));
                        cmd.Parameters.Add(new SqlParameter("@fieldName", field.fieldName));
                        cmd.Parameters.Add(new SqlParameter("@fieldValue", field.fieldValue));

                        // execute the command
                        rdr = cmd.ExecuteReader();

                        while (rdr.Read())
                        {
                            field.id = rdr.GetInt32(0);
                        }
                        rdr.Close();
                    }
                }
                workout = new Workout() { id = workoutId, categoryId = categoryId, name = name, userId = userId, additionalFields = other };
            }
            finally
            {
                if (conn != null)
                    conn.Close();
                if (rdr != null)
                    rdr.Close();
            }
            return workout;
        }

        public Workout getWorkout(int id, bool lazy)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            Workout workout = new Workout();
            try
            {
                conn = Helper.createConnection();

                SqlCommand cmd = new SqlCommand("getWorkout", conn);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@id", id));

                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    workout.id = id;
                    workout.name = rdr.GetString(1);
                    workout.categoryId = rdr.GetInt32(2);
                    workout.userId = rdr.GetInt32(3);

                }
                rdr.Close();

                if (!lazy)
                {
                    List<addWorkoutField> additionalFields = new List<addWorkoutField>();
                    cmd = new SqlCommand("getAdditionalFields", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@workoutId", id));

                    // execute the command
                    rdr = cmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        addWorkoutField field = new addWorkoutField() { id = rdr.GetInt32(0), fieldName = rdr.GetString(1), fieldValue = rdr.GetString(2) };
                        additionalFields.Add(field);
                    }
                    workout.additionalFields = additionalFields;
                }
            }
            finally
            {
                if (conn != null)
                    conn.Close();
                if (rdr != null)
                    rdr.Close();
            }
            return workout;
        }

        public Workout updateWorkout(int id, string name, int categoryId, List<addWorkoutField> other, int userId)
        {
            SqlConnection conn = null;
            Workout workout = null;
            try
            {
                conn = Helper.createConnection();

                SqlCommand cmd = new SqlCommand("updateWorkout", conn);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@id", id));
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@categoryId", categoryId));
                cmd.Parameters.Add(new SqlParameter("@userId", userId));

                cmd.ExecuteNonQuery();

                foreach (addWorkoutField field in other)
                {
                    cmd = new SqlCommand("updateAdditionalFields", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@id", field.id));
                    cmd.Parameters.Add(new SqlParameter("@fieldName", field.fieldName));
                    cmd.Parameters.Add(new SqlParameter("@fieldValue", field.fieldValue));

                    // execute the command
                    cmd.ExecuteNonQuery();
                }
                workout = new Workout() { id = id, categoryId = categoryId, name = name, additionalFields = other };
            }
            finally
            {
                if (conn != null)
                    conn.Close();

            }
            return workout;
        }

        public void deleteWorkout(int id)
        {
            SqlConnection conn = null;

            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("DeleteWorkout", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@id", id));

                // execute the command
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }	
        }

        public List<Workout> getWorkouts()
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            List<Workout> workouts = new List<Workout>();
            try
            {
                conn = Helper.createConnection();

                SqlCommand cmd = new SqlCommand("getWorkouts", conn);

                cmd.CommandType = CommandType.StoredProcedure;

                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Workout workout = new Workout();
                    workout.id = rdr.GetInt32(0);
                    workout.name = rdr.GetString(1);
                    workout.categoryId = rdr.GetInt32(2);
                    workout.userId = rdr.GetInt32(3);
                    workouts.Add(workout);
                }
                rdr.Close();
                /*
                if (!lazy)
                {
                    List<addWorkoutField> additionalFields = new List<addWorkoutField>();
                    cmd = new SqlCommand("getAdditionalFields", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@workoutId", id));

                    // execute the command
                    rdr = cmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        addWorkoutField field = new addWorkoutField() { id = rdr.GetInt32(0), fieldName = rdr.GetString(1), fieldValue = rdr.GetString(2) };
                        additionalFields.Add(field);
                    }
                    workout.additionalFields = additionalFields;
                }
                 */
            }
            finally
            {
                if (conn != null)
                    conn.Close();
                if (rdr != null)
                    rdr.Close();
            }
            return workouts;
        }
    }
}
